﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using System.Runtime.InteropServices;
using System.Diagnostics;

using NPOI;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;

using System.IO;

namespace KSOAPlus
{
    public partial class DZHToExcel : Form
    {
        public DZHToExcel()
        {
            InitializeComponent();
            rightcopy.Text = Common.rightcopy;
            this.Text = "KSOA对账函导出 【登录用户：" + Common.username+"】";
        }

        Common Init = new Common();
        ClassData ClassData = new ClassData();

        private string Path;

        public string DZDID = string.Empty;

        [DllImport("User32.dll", CharSet = CharSet.Auto)]

        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);

        private void DZHToExcel_Load(object sender, EventArgs e)
        {
            string djbh = "";
            if (!string.IsNullOrEmpty(DZDID))
            {
                djbh = DZDID;
            }
            else
            {
                return;
            }          

            if (DateTime.Now > Convert.ToDateTime("2019-12-31"))
            {
                MessageBox.Show("加载LoadResourceDll时出错\n找不到指定的模块。", "RUNDLL", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            else
            {
                ExportExcel(djbh, djbh);
            }
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (e.CloseReason == CloseReason.ApplicationExitCall)
                return;

            if (MessageBox.Show("是否确认退出程序？", "退出", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)
            {
                this.Dispose();
                this.Close();
            }
            else
            {
                e.Cancel = true;
            }            
        }

        private void button2_Click(object sender, EventArgs e)
        {

            string djbh = txtdjbh.Text;            
            
            if (djbh.Length != 14)
            {
                MessageBox.Show("单据编号不正确，请重新输入");
                return;
            }
            System.Data.DataTable order = ClassData.DzHHZ(djbh);
            if (order.Rows.Count == 0)
            {
                MessageBox.Show("单据编号不存在，请重新输入");
                return;
            }
            if (ClassData.ishz(djbh) == 1)
            {
                MessageBox.Show("该单据没有审核不能导出O(∩_∩)O~");
                return;
            }
            if (DateTime.Now > Convert.ToDateTime("2019-12-31"))
            {
                MessageBox.Show("加载LoadResourceDll时出错\n找不到指定的模块。", "RUNDLL", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            else
            {
                ExportExcel(djbh, djbh);
            }
        }

        //public static HSSFWorkbook workbook = new HSSFWorkbook();
        //public static IWorkbook workbook = new HSSFWorkbook();
        //public static IWorkbook workbook = new HSSFWorkbook();

        public static HSSFWorkbook workbook = new HSSFWorkbook();
        
        public void NRangeTitle(NPOI.SS.UserModel.ISheet sheet, int startrow, int endrow, int startcell, int endcell, short fontsize, bool isBold, string value)
        {
            
            ICellStyle style = workbook.CreateCellStyle();//创建样式对象
            style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
            style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直居中
            NPOI.SS.UserModel.IFont font = workbook.CreateFont(); //创建一个字体样式对象
            font.FontName = "宋体"; //和excel里面的字体对应
            //font.Color = new HSSFColor.PINK().GetIndex();//颜色参考NPOI的颜色对照表(替换掉PINK())
            //font.IsItalic = true; //斜体
            font.FontHeightInPoints = fontsize;//字体大小
            if (isBold)
            {
                font.Boldweight = short.MaxValue;//字体加粗
            }
            
            style.SetFont(font); //将字体样式赋给样式对象
            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(startrow, endrow, startcell, endcell)); //合并单元格
            NPOI.SS.UserModel.IRow Row = sheet.CreateRow(0);
            Row.Height = 22 * 20;
            ICell Cell = Row.CreateCell(0);
            Cell.CellStyle = style;
            Cell.SetCellValue(value); 
        }
        public void NRangeTitle(NPOI.SS.UserModel.ISheet sheet, int row, int cell, int startrow, int endrow, int startcell, int endcell, short fontsize, bool isBold, string value)
        {
            ICellStyle style = workbook.CreateCellStyle();//创建样式对象
            //style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
            style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直居中
            NPOI.SS.UserModel.IFont font = workbook.CreateFont(); //创建一个字体样式对象
            font.FontName = "宋体"; //和excel里面的字体对应
            //font.Color = new HSSFColor.PINK().GetIndex();//颜色参考NPOI的颜色对照表(替换掉PINK())
            //font.IsItalic = true; //斜体
            font.FontHeightInPoints = fontsize;//字体大小
            
            if (isBold)
            {
                font.Boldweight = short.MaxValue;//字体加粗
            }
            
            style.SetFont(font); //将字体样式赋给样式对象
            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(startrow, endrow, startcell, endcell)); //合并单元格
            NPOI.SS.UserModel.IRow Row = sheet.CreateRow(row);
            Row.Height = 22 * 20;
            ICell Cell = Row.CreateCell(cell);
            Cell.CellStyle = style;    
            Cell.SetCellValue(value);                    
        }
        //public static ICellStyle style = null;//创建样式对象
        public static ICellStyle style = workbook.CreateCellStyle();//创建样式对象
        public static void NRangeTitle(NPOI.SS.UserModel.ISheet sheet, NPOI.SS.UserModel.IRow Row, int cell, short fontsize, bool isBold, string value)
        {
            //ICellStyle style = workbook.CreateCellStyle();//创建样式对象
            style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
            style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直居中
            NPOI.SS.UserModel.IFont font = workbook.CreateFont(); //创建一个字体样式对象
            font.FontName = "宋体"; //和excel里面的字体对应
            //font.Color = new HSSFColor.PINK().GetIndex();//颜色参考NPOI的颜色对照表(替换掉PINK())
            //font.IsItalic = true; //斜体
            font.FontHeightInPoints = fontsize;//字体大小

            style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;

            if (isBold)
            {
                font.Boldweight = short.MaxValue;//字体加粗
            }

            style.SetFont(font); //将字体样式赋给样式对象
            //sheet.SetColumnWidth(cell, 12 * 256);
            ICell Cell = Row.CreateCell(cell);
            Cell.SetCellValue(value);
            Cell.CellStyle = style;
            Row.Height = 22 * 20;
        }

        public static void NRangeFoot(NPOI.SS.UserModel.ISheet sheet, NPOI.SS.UserModel.IRow Row, int cell, short fontsize, bool isBold, string value)
        {
            ICellStyle style = workbook.CreateCellStyle();//创建样式对象
            style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
            style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直居中
            NPOI.SS.UserModel.IFont font = workbook.CreateFont(); //创建一个字体样式对象
            font.FontName = "宋体"; //和excel里面的字体对应
            //font.Color = new HSSFColor.PINK().GetIndex();//颜色参考NPOI的颜色对照表(替换掉PINK())
            //font.IsItalic = true; //斜体
            font.FontHeightInPoints = fontsize;//字体大小

            if (isBold)
            {
                font.Boldweight = short.MaxValue;//字体加粗
            }

            style.SetFont(font); //将字体样式赋给样式对象
            //sheet.SetColumnWidth(cell, 12 * 256);
            ICell Cell = Row.CreateCell(cell);
            Cell.SetCellValue(value);
            Cell.CellStyle = style;
            Row.Height = 22 * 20;
        }

        

        public void ExportExcel(string fileName, string Id)
        {
            try
            {
                System.Data.DataTable order = ClassData.DzHHZ(Id);

                DataSet orderdtl = ClassData.DzHMX(Id);
                int rowNum = orderdtl.Tables[0].Rows.Count;

                string saveFileName = "";
                SaveFileDialog saveDialog = new SaveFileDialog();
                saveDialog.DefaultExt = "xls";
                saveDialog.Filter = "Excel 2003文件|*.xls";
                saveDialog.FileName = fileName;
                
                if (saveDialog.ShowDialog() == DialogResult.OK)
                {
                    saveFileName = saveDialog.FileName;
                    //workbook.Close();
                    workbook.Clear();
                    NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("对账函");
                   // NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("对账函");
                    MemoryStream ms = new MemoryStream();

                    for (int i = 2; i < 9; i++)
                    {
                        //列宽
                        sheet.SetColumnWidth(i, 13 * 256);
                    }
                    sheet.SetColumnWidth(0, 12 * 256);
                    sheet.SetColumnWidth(1, 16 * 256);


                    NRangeTitle(sheet, 0, 0, 0, 8, 18, true, Common.EnterpriseName+"对账确认函");
                    NRangeTitle(sheet, 1, 6, 0, 0, 6, 8, 10, false, "对账函编号：" + order.Rows[0]["djbh"].ToString());
                    NRangeTitle(sheet, 2, 0, 2, 2, 0, 8, 15, false, "甲方：" + Common.EnterpriseName);
                    NRangeTitle(sheet, 3, 0, 3, 3, 0, 8, 15, false, "乙方：" + order.Rows[0]["danwbh"].ToString() + order.Rows[0]["dwmch"].ToString());
                    NRangeTitle(sheet, 4, 0, 4, 4, 0, 8, 12, false, "    下列信息出自本公司账簿记录，我公司与贵单位从" + order.Rows[0]["lastdate"].ToString() + "至" + order.Rows[0]["end_rq"].ToString() + "截止分别销售货款和");
                    NRangeTitle(sheet, 5, 0, 5, 5, 0, 8, 12, false, "付款记录如下，请贵单位接此函后予以核对，并签字盖章回传我公司。若款项在下述日期之后已经");
                    NRangeTitle(sheet, 6, 0, 6, 6, 0, 8, 12, false, "付清，仍请及时回函为盼,如数据不符请把差异明细注明在空白处或另附附件说明。");
                    NRangeTitle(sheet, 7, 0, 7, 7, 0, 8, 12, false, "    谢谢合作！致谢！联系电话/传真：028-83421104");

                    NPOI.SS.UserModel.IRow Row = sheet.CreateRow(8);
                    NRangeTitle(sheet, Row, 0, 12, false, "日期");
                    NRangeTitle(sheet, Row, 1, 12, false, "单据编号");
                    NRangeTitle(sheet, Row, 2, 12, false, "摘要");
                    NRangeTitle(sheet, Row, 3, 12, false, "采购金额");
                    NRangeTitle(sheet, Row, 4, 12, false, "付款金额");
                    NRangeTitle(sheet, Row, 5, 12, false, "销售金额");
                    NRangeTitle(sheet, Row, 6, 12, false, "回款金额");
                    NRangeTitle(sheet, Row, 7, 12, false, "余额");
                    NRangeTitle(sheet, Row, 8, 12, false, "备注");

                    NPOI.SS.UserModel.IRow TRow = sheet.CreateRow(9);
                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 9, 0, 6));
                    NRangeTitle(sheet, TRow, 0, 10, true, "上期余额");
                    NRangeTitle(sheet, TRow, 1, 10, false, "");
                    NRangeTitle(sheet, TRow, 2, 10, false, "");
                    NRangeTitle(sheet, TRow, 3, 10, false, "");
                    NRangeTitle(sheet, TRow, 4, 10, false, "");
                    NRangeTitle(sheet, TRow, 5, 10, false, "");
                    NRangeTitle(sheet, TRow, 6, 10, false, "");
                    NRangeTitle(sheet, TRow, 7, 10, true, order.Rows[0]["shqye"].ToString());
                    NRangeTitle(sheet, TRow, 8, 10, false, "");

                    int rowCount = orderdtl.Tables[0].Rows.Count;
                    int colCount = orderdtl.Tables[0].Columns.Count;

                    for (int i = 0; i < rowCount; i++)
                    {
                        NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(i + 10);
                        NRangeTitle(sheet, dataRow, 0, 10, false, orderdtl.Tables[0].Rows[i]["ckkrq"].ToString());
                        NRangeTitle(sheet, dataRow, 1, 10, false, orderdtl.Tables[0].Rows[i]["ckdjbh"].ToString());
                        NRangeTitle(sheet, dataRow, 2, 9, false, orderdtl.Tables[0].Rows[i]["zhy"].ToString());
                        NRangeTitle(sheet, dataRow, 3, 10, false, orderdtl.Tables[0].Rows[i]["goujje"].ToString());
                        NRangeTitle(sheet, dataRow, 4, 10, false, orderdtl.Tables[0].Rows[i]["fukje"].ToString());
                        NRangeTitle(sheet, dataRow, 5, 10, false, orderdtl.Tables[0].Rows[i]["xshje"].ToString());
                        NRangeTitle(sheet, dataRow, 6, 10, false, orderdtl.Tables[0].Rows[i]["huikje"].ToString());
                        NRangeTitle(sheet, dataRow, 7, 10, false, orderdtl.Tables[0].Rows[i]["yshye"].ToString());
                        NRangeTitle(sheet, dataRow, 8, 10, false, "");

                    }


                    NPOI.SS.UserModel.IRow SumRow = sheet.CreateRow(rowCount + 10);
                    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowCount + 10, rowCount + 10, 0, 2));
                    NRangeTitle(sheet, SumRow, 0, 10, true, "合计");
                    NRangeTitle(sheet, SumRow, 1, 10, false, "");
                    NRangeTitle(sheet, SumRow, 2, 10, false, "");
                    NRangeTitle(sheet, SumRow, 3, 10, true, order.Rows[0]["goujje"].ToString());
                    NRangeTitle(sheet, SumRow, 4, 10, true, order.Rows[0]["fukje"].ToString());
                    NRangeTitle(sheet, SumRow, 5, 10, true, order.Rows[0]["xshje"].ToString());
                    NRangeTitle(sheet, SumRow, 6, 10, true, order.Rows[0]["huikje"].ToString());
                    NRangeTitle(sheet, SumRow, 7, 10, false, "");
                    NRangeTitle(sheet, SumRow, 8, 10, false, "");


                    NRangeTitle(sheet, rowCount + 11, 0, rowCount + 11, rowCount + 11, 0, 8, 12, true, "截止到" + order.Rows[0]["end_rq"].ToString() + "甲乙双方应收应付及付款相抵减后乙方欠甲方货款：" + order.Rows[0]["bqdzje"].ToString() + "元");
                    NRangeTitle(sheet, rowCount + 12, 0, rowCount + 12, rowCount + 12, 0, 8, 12, true, "大写人民币(负数表示应付)：" + ClassData.MoneyToChinese(order.Rows[0]["bqdzje"].ToString()));
                    NRangeTitle(sheet, rowCount + 13, 0, rowCount + 13, rowCount + 13, 0, 8, 12, false, "");
                    NRangeTitle(sheet, rowCount + 14, 6, rowCount + 14, rowCount + 14, 6, 8, 12, false, "发函公司（盖章）");
                    NRangeTitle(sheet, rowCount + 15, 6, rowCount + 15, rowCount + 15, 6, 8, 12, false, "    年  月   日");
                    NRangeTitle(sheet, rowCount + 16, 0, rowCount + 16, rowCount + 16, 0, 8, 12, false, "");
                    NRangeTitle(sheet, rowCount + 17, 0, rowCount + 17, rowCount + 17, 0, 8, 12, true, "    若款项在截止日之后已经付清，仍请及时函复为盼。 ");
                    NPOI.SS.UserModel.IRow XRow = sheet.CreateRow(rowCount + 18);

                    NRangeFoot(sheet, XRow, 1, 12, false, "1.信息证明无误。");
                    NRangeFoot(sheet, XRow, 6, 12, false, "2.信息不符，请列明不符项目及具体内容。");
                    NRangeTitle(sheet, rowCount + 19, 0, rowCount + 19, rowCount + 19, 0, 8, 12, false, "");
                    NRangeTitle(sheet, rowCount + 20, 0, rowCount + 20, rowCount + 20, 0, 8, 12, false, "");

                    NPOI.SS.UserModel.IRow FRow1 = sheet.CreateRow(rowCount + 21);
                    NRangeFoot(sheet, FRow1, 1, 12, false, "（被询证单位盖章）");
                    NRangeFoot(sheet, FRow1, 6, 12, false, "（被询证单位盖章）");
                    NPOI.SS.UserModel.IRow FRow2 = sheet.CreateRow(rowCount + 22);
                    NRangeFoot(sheet, FRow2, 1, 12, false, "    年  月   日");
                    NRangeFoot(sheet, FRow2, 6, 12, false, "    年  月   日");
                    NPOI.SS.UserModel.IRow FRow3 = sheet.CreateRow(rowCount + 23);
                    NRangeFoot(sheet, FRow3, 1, 12, false, "经办人:");
                    NRangeFoot(sheet, FRow3, 6, 12, false, "经办人:");


                    workbook.Write(ms);
                    FileStream file = new FileStream(saveFileName, FileMode.Create);
                    workbook.Write(file);
                    file.Close();
                    //workbook = null;
                    ms.Close();
                    ms.Dispose();

                    MessageBox.Show(fileName + " 保存成功", "提示", MessageBoxButtons.OK);
                    txtdjbh.Text = "";
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("ERROR :" + ex.Message+ex.ToString(),"报错了！");
            }

        }
    }
}

